PIP Installation
pip install plotly==5.18.0
pip install dash==0.29.0
pip install dash plotly
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
pip install gspread
pip install gspread google-auth
Cell In[1], line 1 pip install plotly==5.18.0 ^ SyntaxError: invalid syntax
Google Authentication
import os.path
import pandas as pd
import plotly.express as px
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
# If modifying these scopes, delete the file token.json.
SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
def create_dataframe(spreadsheet_id, range_name):
"""Create a DataFrame from a Google Spreadsheet."""
creds = None
if os.path.exists("token.json"):
creds = Credentials.from_authorized_user_file("token.json", SCOPES)
if not creds or not creds.valid:
if creds and creds.expired and creds.refresh_token:
creds.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(
"credentials.json", SCOPES
)
creds = flow.run_local_server(port=0)
with open("token.json", "w") as token:
token.write(creds.to_json())
try:
service = build("sheets", "v4", credentials=creds)
# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_name).execute()
values = result.get("values", [])
if not values:
print("No data found.")
return pd.DataFrame() # Return an empty DataFrame if no data is found
# Convert the values to a pandas DataFrame, skipping the first row
df = pd.DataFrame(values[1:], columns=values[0])
return df
except HttpError as err:
print(err)
return pd.DataFrame() # Return an empty DataFrame in case of an error
Net Sales By Region
# Access spreadsheet
SPREADSHEET_ID = "1e1EvgnSxAce8OcCqXCvBsnUQVlCHPIf5wA6e-UvA59c"
RANGE_NAME = "'Net Sales By Market'!A1:C61"
# Call the function to create a new DataFrame
market = create_dataframe(SPREADSHEET_ID, RANGE_NAME)
market = market.replace({',': '', '\.00': ''}, regex=True)
market["Sales"] = market["Sales"].astype(int)
#market.set_index("Year", inplace=True)
# Reverse year to get ascending order
r_market= market.iloc[::-1]
r_market
| Year | Region | Sales | |
|---|---|---|---|
| 59 | 2011 | Rest of Asia Pacific | 9902 |
| 58 | 2012 | Rest of Asia Pacific | 10741 |
| 57 | 2013 | Rest of Asia Pacific | 11181 |
| 56 | 2014 | Rest of Asia Pacific | 11248 |
| 55 | 2015 | Rest of Asia Pacific | 15093 |
| 54 | 2016 | Rest of Asia Pacific | 13654 |
| 53 | 2017 | Rest of Asia Pacific | 15199 |
| 52 | 2018 | Rest of Asia Pacific | 17407 |
| 51 | 2019 | Rest of Asia Pacific | 17788 |
| 50 | 2020 | Rest of Asia Pacific | 19593 |
| 49 | 2021 | Rest of Asia Pacific | 26356 |
| 48 | 2022 | Rest of Asia Pacific | 29375 |
| 47 | 2011 | Japan | 5437 |
| 46 | 2012 | Japan | 10571 |
| 45 | 2013 | Japan | 13462 |
| 44 | 2014 | Japan | 15314 |
| 43 | 2015 | Japan | 15706 |
| 42 | 2016 | Japan | 16928 |
| 41 | 2017 | Japan | 17733 |
| 40 | 2018 | Japan | 21733 |
| 39 | 2019 | Japan | 21506 |
| 38 | 2020 | Japan | 21418 |
| 37 | 2021 | Japan | 28482 |
| 36 | 2022 | Japan | 25977 |
| 35 | 2011 | Greater China | 12690 |
| 34 | 2012 | Greater China | 22533 |
| 33 | 2013 | Greater China | 25417 |
| 32 | 2014 | Greater China | 31853 |
| 31 | 2015 | Greater China | 58715 |
| 30 | 2016 | Greater China | 48492 |
| 29 | 2017 | Greater China | 44764 |
| 28 | 2018 | Greater China | 51942 |
| 27 | 2019 | Greater China | 43678 |
| 26 | 2020 | Greater China | 40308 |
| 25 | 2021 | Greater China | 68366 |
| 24 | 2022 | Greater China | 74200 |
| 23 | 2011 | Europe | 27778 |
| 22 | 2012 | Europe | 36323 |
| 21 | 2013 | Europe | 37883 |
| 20 | 2014 | Europe | 44285 |
| 19 | 2015 | Europe | 50337 |
| 18 | 2016 | Europe | 49952 |
| 17 | 2017 | Europe | 54938 |
| 16 | 2018 | Europe | 62420 |
| 15 | 2019 | Europe | 60288 |
| 14 | 2020 | Europe | 68640 |
| 13 | 2021 | Europe | 89307 |
| 12 | 2022 | Europe | 95118 |
| 11 | 2011 | Americas | 38315 |
| 10 | 2012 | Americas | 57512 |
| 9 | 2013 | Americas | 62739 |
| 8 | 2014 | Americas | 80095 |
| 7 | 2015 | Americas | 93864 |
| 6 | 2016 | Americas | 86613 |
| 5 | 2017 | Americas | 96600 |
| 4 | 2018 | Americas | 112093 |
| 3 | 2019 | Americas | 116914 |
| 2 | 2020 | Americas | 124556 |
| 1 | 2021 | Americas | 153306 |
| 0 | 2022 | Americas | 169658 |
Line Graph
# Create a line graph for sales for each region across years
line_chart = px.line(r_market, x="Year", y="Sales", color="Region", markers=True, title="Sales For Each Region Across Years", height=600, template="plotly_dark")
line_chart.update_traces(mode="markers+lines", hovertemplate=None)
line_chart.update_layout(hovermode="x unified", title_x=0.5)
line_chart.show()
#fig.update_layout(hovermode="closest")
#fig.layout.update(showlegend=False)
#fig.update_layout(title_text='Your title', title_x=0.5)
Bar Graph
# Create a bar chart with custom color and animation
bar_chart = px.bar(
r_market,
x="Region",
y="Sales",
animation_frame="Year",
animation_group="Region",
hover_name="Region",
range_y=[0, 180000],
height=600,
template="plotly_dark",
text_auto='.2s',
title="Sales By Region"
)
bar_chart.update_yaxes(
autorange=False,
tick0=0,
dtick=50000,
visible=False
)
# Set custom color for the region
custom_color = '#4ba65f'
# Loop through frames and update traces with custom color
for i, frame in enumerate(bar_chart.frames):
frame_data = frame.data[0]
frame_data.update(marker_color=custom_color, marker_line_color='white',textposition="outside")
bar_chart.update_traces(marker_color = '#4ba65f', marker_line_color = 'white', textposition="outside")
bar_chart.update_layout(hovermode=False,title_x=0.5, xaxis_title="")
bar_chart.show()
import dash
from dash import dcc
from dash import html
from dash.dependencies import Input, Output
# Dash app
app = dash.Dash(__name__)
# Layout of the dashboard
app.layout = html.Div([
# Second chart (Line chart)
#dcc.Graph(id='line-chart', figure=line_chart),
# First chart (Bar chart)
dcc.Graph(id='bar-chart', figure=bar_chart)
])
if __name__ == '__main__':
app.run_server(port=8051, debug=True)